<?
class Service_Country {
	
	
	/**
	 * Получаем все страны из базы
	 *
	 * @return array
	 */
	public function getAllCountry() {
		$objDB = Service_Database::instance();
		$arrCountrys = $objDB->select("SELECT id_country, name FROM geoip_country /*FORCE INDEX(name)*/ ORDER BY name ASC"); // примерно 250 стран
		return $arrCountrys;
	}
	
	/**
	 * Получаем 10 стран из базы, с учетом текущего местоположения +
	 *
	 * @return array
	 */
	public function getFirstCountry($idCountry = null) {
		$objDB = Service_Database::instance();
		$arrCountrys = $objDB->select("SELECT id_country, name FROM geoip_country LIMIT 0, 10");
		if ($idCountry){
			// если нет выбранной страны
			if (!inAssocArray($idCountry, "id_country", $arrCountrys)){
				$arrCountry = $objDB->selectOne("SELECT id_country, name FROM geoip_country WHERE id_country = $idCountry LIMIT 1");
				// то добавляем ее в выходной массив
				$arrCountrys[] = $arrCountry;
			}
		}
		return $arrCountrys;
	}
	
	/**
	 * получем город по id
	 */
	public static function getCityNameById($idCity) {
		$objDB = Service_Database::instance();
		$stmt = "
		SELECT `geoip_city`.`name`
		FROM `geoip_city`
		WHERE `geoip_city`.`id_city` = " . $idCity . "
		LIMIT 1";
		return $objDB->selectSimpleField($stmt);
	}
	
	/**
	 * функция для получения самых популярных городов опредеенной страны
	 *
	 */
	public static function getFirstCity($idCountry, $idCity = null) {
		if ($idCountry != NULL) {
			$objDB = Service_Database::instance();
			$stmt = "
			SELECT `geoip_city`.`id_city`, `geoip_city`.`name`
			FROM `geoip_city`
			WHERE `geoip_city`.`id_country` = " . $idCountry . "
			AND name != '' 
			LIMIT 11";
			$arrCitys = $objDB->select($stmt);
			
			if ($idCity){
				// если нет выбранного города
				if (!inAssocArray($idCity, "id_city", $arrCitys)){
					$arrCity = $objDB->selectOne("SELECT id_city, name FROM geoip_city WHERE id_city = $idCity LIMIT 1");
					// то добавляем ее в выходной массив
					$arrCitys[] = $arrCity;
				}
			}
			
			return $arrCitys;
			
		}
		else
			return NULL;
	}

	public static function getIdCountryFromIdCity($idCity){
		$objDB = Service_Database::instance();
		$stmt = "SELECT id_country FROM geoip_city WHERE id_city = $idCity";
		return $objDB->selectSimpleField($stmt);	
	}
	
	/**
	 * Метод формирует список тэгов option для построения selecta с выбором страны
	 *
	 */
	public static function getOptionsForCountrySelect($arrCountry, $idSelectedCountry = null) {
		$option = "";
		$selectedText = "";
		$size = sizeof($arrCountry);
		
		// временно
		if (!$idSelectedCountry) $idSelectedCountry = 27; // Russian
		
		for ($i=0; $i<$size; $i++){
			if ($arrCountry[$i]["id_country"] == $idSelectedCountry) {
				$selectedText = "selected: selected";
			}
			$option .= "<option value=\"".$arrCountry[$i]["id_country"]."\" $selectedText>".$arrCountry[$i]["name"]."</option>";
			$selectedText = "";
		}
		return $option;
	}
	
	public function getAllCity($idCountry = null) {
		$objDB = Service_Database::instance();
		
		if ($idCountry){
			$stmt = "SELECT id_city, name FROM geoip_city WHERE id_country = ".$idCountry." AND name != '' ORDER BY name ASC ";
		} else {
			$stmt = "SELECT id_city, name FROM geoip_city WHERE id_country = 27 AND name != '' LIMIT 0,100";
		}
		
		return $objDB->select($stmt);
	}
	
	public static function getOptionsForCitySelect($arrCity, $idSelectedCity = null) {
		$option = "";
		$selectedText = "";
		$size = sizeof($arrCity);
		
		
		if (!$idSelectedCity) $idSelectedCity = 32497; // Saint-Petersburg
		
		for ($i=0; $i<$size; $i++){
			if ($arrCity[$i]["id_city"] == $idSelectedCity) {
				$selectedText = "selected: selected";
			}
			$option .= "<option value=\"".$arrCity[$i]["id_city"]."\" $selectedText>".$arrCity[$i]["name"]."</option>";
			$selectedText = "";
		}
		return $option;
	}
	
	public static function getALLFromIP($addr) {
		$objDB = Service_Database::instance();
		// this sprintf() wrapper is needed, because the PHP long is signed by default
		$ipnum = sprintf("%u", ip2long($addr));
		$query = "SELECT id_city, name FROM geoip_city_ip FORCE INDEX(ip_start, ip_end) NATURAL JOIN geoip_city WHERE ${ipnum} BETWEEN ip_start AND ip_end";
		return $objDB->selectOne($query);
	}

/*	function getCCfromIP($addr) {
		$data = getALLfromIP($addr);
		if($data) return $data['cc'];
			return false;
	}*/

	public static function getCityFromIP($addr) {
		$data = self::getALLFromIP($addr);
		if($data) return $data['name'];
			return false;
	}
	
	private static function _getIdCityFromIP($addr) {
		$data = self::getALLFromIP($addr);
		if($data) return $data['id_city'];
			return false;
	}
	
	public static function getIdCityFromIP(){
		$remote_address = $_SERVER['REMOTE_ADDR'];
		$remote_address = "78.36.255.255";
		return self::_getIdCityFromIP($remote_address);
	}

/*	function getCCfromNAME($name) {
		$addr = gethostbyname($name);
		return getCCfromIP($addr);
	}*/

/*	function getCOUNTRYfromNAME($name) {
		$addr = gethostbyname($name);
		return getCOUNTRYfromIP($addr);
	}*/

}
?>